package com.landinfo.service.impl;

import com.alibaba.fastjson.JSON;
import com.esri.core.geometry.Geometry;
import com.esri.core.geometry.GeometryEngine;
import com.google.gson.Gson;
import com.google.gson.GsonBuilder;
import com.landinfo.repository.DynamicTemplateRepository;
import com.landinfo.service.GrassAuthenticRightService;
import org.hibernate.collection.internal.PersistentSet;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;


@Service
public class GrassAuthenticRightServiceImpl implements GrassAuthenticRightService {

    private final Logger log = LoggerFactory.getLogger(GrassAuthenticRightService.class);

    /**
     * 草原确权
     */
    private final String CAOYUANQUEQUAN = "caoyuanquequan";

    /**
     * 牧户信息采集
     */
    private final String MUHUXINXICAIJI = "muhuxinxicaiji";

    /**
     * 联户信息采集
     */
    private final String LIANHUCHENGYUANXINXICAIJI = "lianhuchengyuanxinxicaiji";

    /**
     * 联户勘测表
     */
    private final String LIANHUKANCEBIAO = "lianhukancebiao";

    /**
     * 公共用地勘测表
     */
    private final String GONGGONGYONGDIKANCEBIAO = "gonggongyongdikancebiao";

    /**
     * 单位用地勘测表
     */
    private final String DANWEIYONGDIKANCEBIAO = "danweiyongdikancebiao";

    /**
     * 寺庙用地勘测表
     */
    private final String SIMIAOYONGDIKANCEBIAO = "simiaoyongdikancebiao";


    @Autowired
    private DynamicTemplateRepository dynamicTemplateRepository;

    /**
     * 根据类型获取历史记录
     *
     * @param type
     * @return
     */
    @Override
    public List getHistory(String type, String userName) {
        String hql = String.format("select pguid,max(createDateTime) from %s where 权属类型='%s' group by pguid ", CAOYUANQUEQUAN, type);
        List<Object> dynamicTable = this.dynamicTemplateRepository.createQueryList(hql); //session.createQuery(hql).list();
        return dynamicTable;
    }

    @Override
    public HashMap<String, Object> Query(String type, String where, String geom) throws Exception {
        HashMap<String, Object> hashMap = new HashMap<>();
        String tableName = "";
        switch (type) {
            case "muhu":
                tableName = MUHUXINXICAIJI;
                break;
            case "lianhu":
                tableName = LIANHUKANCEBIAO;
                break;
            case "gonggong":
                tableName = GONGGONGYONGDIKANCEBIAO;
                break;
            case "simiao":
                tableName = SIMIAOYONGDIKANCEBIAO;
                break;
            case "danwei":
                tableName = DANWEIYONGDIKANCEBIAO;
                break;
        }
        if (where == null || "".equals(where)) {
            where = "1=1";
        }
        if (geom != null && !"".equals(geom)) {
            Geometry geometry = null;
            try {
                geometry = GeometryEngine.jsonToGeometry(geom).getGeometry();
            } catch (Exception ex) {
                throw new Exception("Geometry序列化失败");
            }
            geom = GeometryEngine.geometryToWkt(geometry, 0);
        }

        String sql = String.format("select a.* FROM %s as a left join %s as b on a.pguid=b.pguid where %s and ST_Contains(ST_GeomFromText('%s',4326),b.geom) ", tableName, CAOYUANQUEQUAN, where, geom);
        if (geom == null || "".equals(geom)) {
            if (where == null || "1=1".equals(where)) {
                sql = String.format("select * FROM %s", tableName);
            } else {
                sql = String.format("select a.* FROM %s as a where %s ", tableName, where);
            }
        }
        String sqlDomain = String.format("SELECT json_content FROM dynamic_template where name='%s'", tableName);

        List listDomain = this.dynamicTemplateRepository.createSqlQueryList(sqlDomain);
        List list = this.dynamicTemplateRepository.createSqlQueryList(sql);
        hashMap.put("success", true);
        hashMap.put("rows", list);
        if (listDomain != null && listDomain.size() > 0) {
            hashMap.put("fieldColumnsDomain", listDomain.get(0));
        } else {
            hashMap.put("fieldColumnsDomain", "");
        }
        return hashMap;
    }

    /**
     * 草场承包责任制落实情况汇总表
     *
     * @param type
     * @return
     */
    @Override
    public String getImplementationOfTheSituation(String type, String xzqhCode) {
        String hql = "";
        switch (type) {
            case "city":
                hql = String.format("select max(a.所在区县名称),count(a.所在区县),sum(a.冬春面积),sum(a.夏秋面积),count(b.所在区县),sum(b.冬春面积),sum(b.夏秋面积),count(c.所在区县),sum(c.冬春面积),sum(c.夏秋面积),count(d.所在区县),sum(d.冬春面积),sum(d.夏秋面积),count(e.所在区县),sum(e.冬春面积),sum(e.夏秋面积) from %s as a left join %s as b  on a.所在区县=b.所在区县 left join %s as c on a.所在区县=c.所在区县 left join %s as d on a.所在区县=d.所在区县 left join %s as e on a.所在区县=e.所在区县 group by a.所在区县",
                        MUHUXINXICAIJI, LIANHUCHENGYUANXINXICAIJI, GONGGONGYONGDIKANCEBIAO, DANWEIYONGDIKANCEBIAO, SIMIAOYONGDIKANCEBIAO, xzqhCode);
                break;
            case "country":
                hql = String.format("select max(a.所在乡镇名称),count(a.所在乡镇),sum(a.冬春面积),sum(a.夏秋面积),count(b.所在乡镇),sum(b.冬春面积),sum(b.夏秋面积),count(c.所在乡镇),sum(c.冬春面积),sum(c.夏秋面积),count(d.所在乡镇),sum(d.冬春面积),sum(d.夏秋面积),count(e.所在乡镇),sum(e.冬春面积),sum(e.夏秋面积) from %s as a left join %s as b  on a.所在乡镇=b.所在乡镇 left join %s as c on a.所在乡镇=c.所在乡镇 left join %s as d on a.所在乡镇=d.所在乡镇 left join %s as e on a.所在乡镇=e.所在乡镇 where a.所在区县='%s' group by a.所在乡镇 ",
                        MUHUXINXICAIJI, LIANHUCHENGYUANXINXICAIJI, GONGGONGYONGDIKANCEBIAO, DANWEIYONGDIKANCEBIAO, SIMIAOYONGDIKANCEBIAO, xzqhCode);
                break;
            case "town":
                hql = String.format("select max(a.所在村名称),count(a.所在村),sum(a.冬春面积),sum(a.夏秋面积),count(b.所在村),sum(b.冬春面积),sum(b.夏秋面积),count(c.所在村),sum(c.冬春面积),sum(c.夏秋面积),count(d.所在村),sum(d.冬春面积),sum(d.夏秋面积),count(e.所在村),sum(e.冬春面积),sum(e.夏秋面积) from %s as a left join %s as b  on a.所在村=b.所在村 left join %s as c on a.所在村=c.所在村 left join %s as d on a.所在村=d.所在村 left join %s as e on a.所在村=e.所在村 where a.所在乡镇='%s' group by a.所在村 ",
                        MUHUXINXICAIJI, LIANHUCHENGYUANXINXICAIJI, GONGGONGYONGDIKANCEBIAO, DANWEIYONGDIKANCEBIAO, SIMIAOYONGDIKANCEBIAO, xzqhCode);
                break;
        }
        List list = this.dynamicTemplateRepository.createQueryList(hql);
        return JSON.toJSONString(list);
    }

    /**
     * 草原承包情况汇总表
     * 1.村草原承包统计表
     * 2.全乡草原承包汇总表
     *
     * @param type
     * @return
     */
    @Override
    public String getGrassContractedTotal(String type, String village) {
        String hql = "";
        // 承包草地面积
        // (冬春面积+夏秋面积) as 总面积
        switch (type) {
            case "village":
                hql = String.format("select 户主姓名,原承包合同编号,家庭人口, (承包草地面积) as 总面积, 冬春面积,夏秋面积,备注 from %s where 所在村='%s'", MUHUXINXICAIJI, village);
                break;
            case "town":
                hql = String.format("select max(所在村名称),count(*) as 户数,sum(家庭人口) as 家庭人口,sum(承包草地面积) as 草原总面积,sum(冬春面积+夏秋面积) as 总面积,sum(冬春面积) as 冬春面积,sum(夏秋面积) as 夏秋面积 from %s where 所在乡镇='%s' group by 所在村 ", MUHUXINXICAIJI, village);
                break;
        }
        List list = this.dynamicTemplateRepository.createQueryList(hql);
        String result = JSON.toJSONString(list);
        return result;
    }

    /**
     * 所有证统计  ，这个是统计发证的，我们app与证书无关，统计不了
     *
     * @param type
     * @return
     */
    @Override
    public String getAllEvidenceTotal(String type) {
        return null;
    }

    /**
     * 使用证统计  ，这个是统计发证的，我们app与证书无关，统计不了
     *
     * @param type
     * @return
     */
    @Override
    public String getUsePermitTotal(String type) {
        return null;
    }

    /**
     * 以下三个为一组
     * 户证件统计
     * 联户证统计
     * 村组证件统计
     *
     * @param type
     * @param gxb
     * @param xcmcl @return
     */
    @Override
    public List getJointHouseholdsTotal(String type, String village, Double gxb, Double xcmcl) {
        String hql = null;
        // 理论载畜量=鲜草亩产量*总面积/3.3(干鲜比)/657公斤(一年吃的东西，是个常量)
        // 实际载畜量=草食生畜总数*3.0924
        // 超载牲畜量=实际-理论
        List list = null;
        switch (type) {
            case "muhu":
            case "牧户":
                // ,(东至+西至+南至+北至) as 边界文字说明
                hql = String.format("select 户主姓名,原承包合同编号,草场名称,承包草地面积,冬春面积,夏秋面积,(%f*承包草地面积/%f/657) as 理论载畜量,(草食牲畜数量合计*3.0924) as 实际载畜量,(草食牲畜数量合计*3.0924-%f*承包草地面积/%f/657) as 超载牲畜量,填表日期,调查人 from %s where 所在村='%s'", xcmcl, gxb, xcmcl, gxb, MUHUXINXICAIJI, village);
                list = this.dynamicTemplateRepository.createQueryList(hql);
                break;
            case "lianhu":
            case "联户":
                hql = String.format("select 联户主代表姓名,原承包证号,草场名称,承包草原面积,冬春面积,夏秋面积,(%f*承包草原面积/%f/657) as 理论载畜量,(草食牲畜数量合计*3.0924) as 实际载畜量,(草食牲畜数量合计*3.0924-%f*承包草原面积/%f/657) as 超载牲畜量,填表日期,调查人 from %s where 所在村='%s'", xcmcl, gxb, xcmcl, gxb, LIANHUCHENGYUANXINXICAIJI, village);
                list = this.dynamicTemplateRepository.createQueryList(hql);
                break;
            case "cunzu":
            case "村组":
                // 村组统计
                // hql = String.format("select 所在村,max(原承包证号),max(草场名称),count(承包草原面积),count(冬春面积),count(夏秋面积),count(%f*承包草地面积/%f/657) as 理论载畜量,count(草食牲畜数量合计*3.0924) as 实际载畜量,count(草食牲畜数量合计*3.0924-%f*承包草地面积/%f/657) as 超载牲畜量,max(填表日期),max(调查人) from muhuxinxicaiji group by 所在村");
                // List tempList1= this.dynamicTemplateRepository.createQueryList(hql);
                // hql = String.format("select 所在村,max(原承包证号),max(草场名称),count(承包草原面积),count(冬春面积),count(夏秋面积),count(%f*承包草地面积/%f/657) as 理论载畜量,count(草食牲畜数量合计*3.0924) as 实际载畜量,count(草食牲畜数量合计*3.0924-%f*承包草地面积/%f/657) as 超载牲畜量,max(填表日期),max(调查人) from lianhuchengyuanxinxicaiji group by 所在村");
                // List tempList2= this.dynamicTemplateRepository.createQueryList(hql);
                //hql = String.format("select a.户主姓名 from muhuxinxicaiji a,lianhuchengyuanxinxicaiji b where a.pguid=b.pguid");

//                hql = String.format("select 所在村,max(原承包合同编号),max(草场名称),sum(承包草地面积),sum(冬春面积),sum(夏秋面积),(sum(承包草地面积)*%f/%f/657) as 理论载畜量,(sum(草食牲畜数量合计)*3.0924) as 实际载畜量,((sum(草食牲畜数量合计)*3.0924)-(sum(承包草地面积)*%f/%f/657)) as 超载牲畜量,max(填表日期),max(调查人) from muhuxinxicaiji group by 所在村", xcmcl, gxb, xcmcl, gxb);
//                List tempListMh = this.dynamicTemplateRepository.createQueryList(hql);
//                // 联户
//                hql = String.format("select 所在村,max(原承包证号),max(草场名称),sum(承包草原面积),sum(冬春面积),sum(夏秋面积),(sum(承包草原面积)*%f/%f/657) as 理论载畜量,(sum(草食牲畜数量合计)*3.0924) as 实际载畜量,((sum(草食牲畜数量合计)*3.0924)-(sum(承包草原面积)*%f/%f/657)) as 超载牲畜量,max(填表日期),max(调查人) from lianhuchengyuanxinxicaiji group by 所在村", xcmcl, gxb, xcmcl, gxb);
//                List tempListLh = this.dynamicTemplateRepository.createQueryList(hql);
//                if (tempListMh == null) {
//                    list = tempListLh;
//                }
//                if (tempListLh == null) {
//                    list = tempListMh;
//                }
//                if (tempListMh != null && tempListLh != null) {
//                    for (int i = 0; i < tempListMh.size(); i++) {
//                        Object[] tempListMhObjects = (Object[]) tempListMh.get(i);
//                        if (tempListMhObjects[0] == null) {
//                            continue;
//                        }
//                        String villageNameMh = tempListMhObjects[0].toString();
//                        Object[] tempObjects = null;
//                        for (int j = 0; j < tempListLh.size(); j++) {
//                            Object[] tempListLhObjects = (Object[]) tempListLh.get(j);
//                            if (tempListLhObjects[0] == null) {
//                                continue;
//                            }
//                            String villageNameLh = tempListLhObjects[0].toString();
//                            if (villageNameMh.equals(villageNameLh)) {
//                                tempObjects = (Object[]) tempListLh.get(j);
//                                break;
//                            }
//                        }
//                        if (tempObjects != null) {
//                            for (int index = 3; index <= 8; index++) {
//                                Object[] tempObjects2 = (Object[]) tempListMh.get(i);
//                                tempObjects2[index] = Double.parseDouble(tempObjects2[index].toString()) + Double.parseDouble(tempObjects[index].toString());
//                            }
//                        }
//                    }
//                    list = tempListMh;
//                }
                //
                hql = String.format("select max(a.所在村名称),max(a.原承包合同编号),max(a.草场名称),sum(a.承包草地面积+b.承包草原面积),sum(a.冬春面积+b.冬春面积),sum(a.夏秋面积+b.夏秋面积),(sum(a.承包草地面积+b.承包草原面积)*%f/%f/657) as 理论载畜量,(sum(a.草食牲畜数量合计+b.草食牲畜数量合计)*3.0924) as 实际载畜量,((sum(a.草食牲畜数量合计+b.草食牲畜数量合计)*3.0924)-(sum(a.承包草地面积+b.承包草原面积)*%f/%f/657)) as 超载牲畜量,max(a.填表日期),max(a.调查人) from %s as a left join %s as b on a.所在村=b.所在村 where a.所在村='%s' group by a.所在村 ", xcmcl, gxb, xcmcl, gxb, MUHUXINXICAIJI, LIANHUCHENGYUANXINXICAIJI, village);
                list = this.dynamicTemplateRepository.createQueryList(hql);
                break;
        }
        return list;
    }

    /**
     * 草场承包基本情况调查表
     *
     * @param village
     * @return
     */
    @Override
    public String getSituationTotal(String village) {
        String hql = String.format("select 所在村名称,户主姓名,家庭人口,劳动力,承包草地面积,退耕还草地面积,(冬春面积+夏秋面积),冬春面积,夏秋面积,草食牲畜数量合计,牛,马,羊,驴骡,备注 from %s where 所在村='%s'", MUHUXINXICAIJI, village);

        List list = this.dynamicTemplateRepository.createQueryList(hql);
        return JSON.toJSONString(list);
    }


}
